/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pv1682010;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.NavigableSet;
import java.util.SortedSet;
import java.util.TreeSet;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Isamaru
 */
public class CourseManager {

    private DataSource dataSource;
    private Logger log = LoggerFactory.getLogger(CourseManager.class);
    public static final String DB_COURSE_TABLE = "courses";
    public static final String DB_COURSE_TEACHERS_TABLE = "course_teachers";
    public static final String DB_COURSE_ID_COLUMN = "course_id";
    public static final String DB_COURSE_NAME_COLUMN = "course_name";
    public static final String DB_COURSE_DESCRIPTION_COLUMN = "course_description";
    public static final String DB_EVENT_TABLE = "events";
    public static final String DB_EVENT_NUMBER_COLUMN = "event_number";
    public static final String DB_EVENT_NAME_COLUMN = "event_name";
    public static final String DB_EVENT_START_COLUMN = "event_start";
    public static final String DB_EVENT_END_COLUMN = "event_end";
    public static final String DB_EVENT_USER_TABLE = "event_attends";
    public static final String DB_COURSE_STUDENT_TABLE = "course_enrolled";

    public CourseManager(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public CourseManager() {
        this(null);
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        log.debug("Data Source set to {}.", dataSource);
    }

    public List<Course> getAllCourses() throws SQLException {
        log.trace("getAllCourses() entered");
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.prepareStatement(
                    "SELECT * FROM " + DB_COURSE_TABLE
                    + " NATURAL LEFT OUTER JOIN " + DB_COURSE_TEACHERS_TABLE
                    + " NATURAL LEFT OUTER JOIN " + UserManager.DB_USER_TABLE
                    + " ORDER BY " + DB_COURSE_ID_COLUMN);
            ResultSet rs = statement.executeQuery();
            log.debug("Statement {} executed", statement);
            Course course = null;
            Collection<User> teachers = null;
            List<Course> courses = new LinkedList<Course>();
            while (rs.next()) {
                if (course == null || (rs.getInt(DB_COURSE_ID_COLUMN) != course.getId())) {
                    Integer id = rs.getInt(DB_COURSE_ID_COLUMN);
                    String name = rs.getString(DB_COURSE_NAME_COLUMN);
                    String description = rs.getString(DB_COURSE_DESCRIPTION_COLUMN);
                    teachers = new LinkedList<User>();
                    course = new Course(id, name, description, teachers);
                    courses.add(course);
                    log.debug("Course {} with id {} retrieved", name, id);
                }
                Integer teacherUco = rs.getInt(UserManager.DB_USER_UCO_COLUMN);
                if (teacherUco > 0) {
                    String teacherName = rs.getString(UserManager.DB_USER_NAME_COLUMN);
                    Boolean teacherAdmin = rs.getBoolean(UserManager.DB_USER_ADMINISTRATOR_COLUMN);
                    teachers.add(new User(teacherUco, teacherName, teacherAdmin));
                    log.trace("Teacher {} retrieved for course {}", teacherName, course.getName());
                }
            }
            return courses;
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }

    }

    public Course getCourseById(Integer id) throws SQLException {
        log.trace("getCourseById() entered");
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.prepareStatement(
                    "SELECT * FROM " + DB_COURSE_TABLE
                    + " NATURAL LEFT OUTER JOIN " + DB_COURSE_TEACHERS_TABLE
                    + " NATURAL LEFT OUTER JOIN " + UserManager.DB_USER_TABLE
                    + " WHERE " + DB_COURSE_TABLE + "." + DB_COURSE_ID_COLUMN + "=?");
            statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            log.debug("statement {} executed", statement);
            Course course = null;
            Collection<User> teachers = new LinkedList<User>();
            while (rs.next()) {
                if (course == null) {
                    String name = rs.getString(DB_COURSE_NAME_COLUMN);
                    String description = rs.getString(DB_COURSE_DESCRIPTION_COLUMN);
                    course = new Course(id, name, description, teachers);
                }
                Integer teacherUco = rs.getInt(UserManager.DB_USER_UCO_COLUMN);
                if (!rs.wasNull()) {
                    String teacherName = rs.getString(UserManager.DB_USER_NAME_COLUMN);
                    Boolean teacherAdmin = rs.getBoolean(UserManager.DB_USER_ADMINISTRATOR_COLUMN);
                    teachers.add(new User(teacherUco, teacherName, teacherAdmin));
                }
            }
            return course;
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void addCourse(Course course) throws SQLException {
        log.trace("addCourse({}) entered", course);
        if (course == null) {
            throw new NullPointerException("course");
        }
        if (course.getId() != null) {
            throw new IllegalArgumentException("Id for course already exists!");
        }
        PreparedStatement statement = null;
        Connection conn = null;
        try {
            //start Transaction
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            //insert Course
            statement = conn.prepareStatement(
                    "INSERT INTO " + DB_COURSE_TABLE
                    + " ( " + DB_COURSE_NAME_COLUMN + " , " + DB_COURSE_DESCRIPTION_COLUMN + ") VALUES "
                    + " (?, ?)", Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, course.getName());
            statement.setString(2, course.getDescription());
            statement.executeUpdate();
            //get Keys
            ResultSet rs = statement.getGeneratedKeys();
            rs.next();
            Integer courseId = rs.getInt(1);
            //insert Teacher relations
            statement = conn.prepareStatement(
                    "INSERT INTO " + DB_COURSE_TEACHERS_TABLE
                    + " ( " + DB_COURSE_ID_COLUMN + ", " + UserManager.DB_USER_UCO_COLUMN + " ) VALUES"
                    + " (?, ?)");
            statement.setInt(1, courseId);
            for (User teacher : course.getTeachers()) {
                statement.setInt(2, teacher.getUco());
                statement.executeUpdate();
            }
            conn.commit();
            course.setId(courseId);
            log.info("Added course {}.", course);
        } finally {
            if (course.getId() == null) {
                log.warn("Adding course failed: rollback.");
                conn.rollback();
            }
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void removeCourse(Course course) throws SQLException {
        log.trace("removeCourse({}) entered", course);
        if (course.getId() == null) {
            throw new IllegalArgumentException("Id for course does not exists!");
        }
        PreparedStatement statement = null;
        Connection conn = null;
        try {
            //start Transaction
            conn = dataSource.getConnection();
            //delete Course (FK constraints will cascade delete)
            statement = conn.prepareStatement(
                    "DELETE FROM " + DB_COURSE_TABLE
                    + " WHERE " + DB_COURSE_ID_COLUMN + "=?");
            statement.setInt(1, course.getId());
            statement.executeUpdate();
            course.setId(null);
            log.info("Course {} deleted.", course);
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void updateCourse(Course course) throws SQLException {
        log.trace("updateCourse({}) entered", course);
        if (course.getId() == null) {
            throw new IllegalArgumentException("Id for course does not exists!");
        }
        PreparedStatement statement = null;
        Connection conn = null;
        boolean success = false;
        try {
            //start Transaction
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            //insert Course
            statement = conn.prepareStatement(
                    "UPDATE " + DB_COURSE_TABLE
                    + " SET " + DB_COURSE_NAME_COLUMN + "=? , " + DB_COURSE_DESCRIPTION_COLUMN + "=? "
                    + " WHERE " + DB_COURSE_ID_COLUMN + "=?");
            statement.setString(1, course.getName());
            statement.setString(2, course.getDescription());
            statement.setInt(3, course.getId());
            statement.executeUpdate();
            //remove Teacher relations
            statement = conn.prepareStatement(
                    "DELETE FROM " + DB_COURSE_TEACHERS_TABLE
                    + " WHERE " + DB_COURSE_ID_COLUMN + "=?");
            statement.setInt(1, course.getId());
            statement.executeUpdate();
            //insert Teacher relations
            statement = conn.prepareStatement(
                    "INSERT INTO " + DB_COURSE_TEACHERS_TABLE
                    + " ( " + DB_COURSE_ID_COLUMN + ", " + UserManager.DB_USER_UCO_COLUMN + " ) VALUES"
                    + " (?, ?)");
            statement.setInt(1, course.getId());
            for (User teacher : course.getTeachers()) {
                statement.setInt(2, teacher.getUco());
                statement.executeUpdate();
            }
            log.info("Course updated to {}.", course);
            conn.commit();
            success = true;
        } finally {
            if (!success) {
                log.warn("Updating course failed: rollback.");
                conn.rollback();
            }
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public NavigableSet<Event> getEventsForCourse(Course course) throws SQLException {
        log.trace("getEventsForCourse({}) entered", course);
        if (course.getId() == null) {
            log.error("Attempting to retrieve events for course {} without id.", course);
            throw new IllegalArgumentException("Course doesn't have id.");
        }
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.prepareStatement(
                    "SELECT * FROM " + DB_COURSE_TABLE
                    + " NATURAL JOIN " + DB_EVENT_TABLE
                    + " WHERE " + DB_COURSE_TABLE + "." + DB_COURSE_ID_COLUMN + "=?");
            statement.setInt(1, course.getId());
            ResultSet rs = statement.executeQuery();
            log.debug("statement {} executed", statement);
            //Course course = null;
            NavigableSet<Event> events = new TreeSet<Event>();
            while (rs.next()) {
                String name = rs.getString(DB_EVENT_NAME_COLUMN);
                int eventNumber = rs.getInt(DB_EVENT_NUMBER_COLUMN);
                Date eventStart = rs.getTimestamp(DB_EVENT_START_COLUMN);
                Date eventEnd = rs.getTimestamp(DB_EVENT_END_COLUMN);
                events.add(new Event(course, eventNumber, name, eventStart, eventEnd));
            }
            return events;
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public Event getEventForCourse(Course course, int eventNumber) throws SQLException {
        log.trace("getEventsForCourse({},{}) entered", course, eventNumber);
        if (course.getId() == null) {
            log.error("Attempting to retrieve events for course {} without id.", course);
            throw new IllegalArgumentException("Course doesn't have id.");
        }
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.prepareStatement(
                    "SELECT * FROM " + DB_COURSE_TABLE
                    + " NATURAL JOIN " + DB_EVENT_TABLE
                    + " WHERE " + DB_COURSE_TABLE + "." + DB_COURSE_ID_COLUMN + "=?"
                    + " AND " + DB_EVENT_TABLE + "." + DB_EVENT_NUMBER_COLUMN + "=?");
            statement.setInt(1, course.getId());
            statement.setInt(2, eventNumber);
            ResultSet rs = statement.executeQuery();
            log.debug("statement {} executed", statement);
            //Course course = null;
            rs.next();
            String name = rs.getString(DB_EVENT_NAME_COLUMN);
            eventNumber = rs.getInt(DB_EVENT_NUMBER_COLUMN);
            Date eventStart = rs.getTimestamp(DB_EVENT_START_COLUMN);
            Date eventEnd = rs.getTimestamp(DB_EVENT_END_COLUMN);
            return new Event(course, eventNumber, name, eventStart, eventEnd);
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void addEvent(Event event) throws SQLException {
        log.trace("addEvent({}) entered", event);
        Course course = event.getCourse();
        if (course.getId() == null) {
            throw new IllegalArgumentException("Course must already exist in table!");
        }
        PreparedStatement statement = null;
        Connection conn = null;
        try {
            //start Transaction
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            // find out number
            Integer eventNumber = 1;
            SortedSet<Event> events = getEventsForCourse(course);
            if (!events.isEmpty()) {
                eventNumber = events.last().getEventNumber() + 1;
            }
            //insert Event
            statement = conn.prepareStatement(
                    "INSERT INTO " + DB_EVENT_TABLE
                    + //                     + "("
                    //			+ DB_COURSE_ID_COLUMN + ","
                    //			+ DB_EVENT_NUMBER_COLUMN + ","
                    //			+ DB_EVENT_NAME_COLUMN +  ","
                    //			+ DB_EVENT_START_COLUMN + ","
                    //			+ DB_EVENT_END_COLUMN + ","
                    //		     + ")" +
                    " VALUES " + " (?, ?, ?, ?, ?)");
            statement.setInt(1, course.getId());
            statement.setInt(2, eventNumber);
            statement.setString(3, event.getEventName());
            Timestamp startTimeStamp = new Timestamp(event.getStartDate().getTime());
            statement.setTimestamp(4, startTimeStamp);
            Timestamp endTimeStamp = new Timestamp(event.getEndDate().getTime());
            statement.setTimestamp(5, endTimeStamp);
            statement.executeUpdate();
            conn.commit();
            event.setEventNumber(eventNumber);
            log.info("Added event {}.", event);
        } finally {
            if (event.getEventNumber() == null) {
                log.warn("Adding event failed: rollback.");
                conn.rollback();
            }
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void updateEvent(Event event) throws SQLException {
        log.trace("updateEvent({}) entered", event);
        if (event.getEventNumber() == null) {
            throw new IllegalArgumentException("Number for event does not exists!");
        }
        if (event.getCourse().getId() == null) {
            throw new IllegalArgumentException("Course id for event does not exists!");
        }
        PreparedStatement statement = null;
        Connection conn = null;
        boolean success = false;
        try {
            //start Transaction
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            //update Event
            statement = conn.prepareStatement(
                    "UPDATE " + DB_EVENT_TABLE
                    + " SET " + DB_EVENT_NAME_COLUMN + "=? , "
                    + DB_EVENT_START_COLUMN + "=? , "
                    + DB_EVENT_END_COLUMN + "=? "
                    + " WHERE " + DB_COURSE_ID_COLUMN + "=? AND "
                    + DB_EVENT_NUMBER_COLUMN + "=? ");
            statement.setString(1, event.getEventName());
            Timestamp startTimeStamp = new Timestamp(event.getStartDate().getTime());
            statement.setTimestamp(2, startTimeStamp);
            Timestamp endTimeStamp = new Timestamp(event.getEndDate().getTime());
            statement.setTimestamp(3, endTimeStamp);
            statement.setInt(4, event.getCourse().getId());
            statement.setInt(5, event.getEventNumber());
            statement.execute();
            log.info("Event updated to {}.", event);
            conn.commit();
            success = true;
        } finally {
            if (!success) {
                log.warn("Updating event failed: rollback.");
                conn.rollback();
            }
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void removeEvent(Event event) throws SQLException {
        log.trace("removeEvent({}) entered", event);
        if (event.getEventNumber() == null) {
            throw new IllegalArgumentException("Number for event does not exists!");
        }
        if (event.getCourse().getId() == null) {
            throw new IllegalArgumentException("Course id for event does not exists!");
        }
        PreparedStatement statement = null;
        Connection conn = null;
        try {
            //start Transaction
            conn = dataSource.getConnection();
            //delete Course (FK constraints will cascade delete)
            statement = conn.prepareStatement(
                    "DELETE FROM " + DB_EVENT_TABLE
                    + " WHERE " + DB_COURSE_ID_COLUMN + "=? AND "
                    + DB_EVENT_NUMBER_COLUMN + "=? ");
            statement.setInt(1, event.getCourse().getId());
            statement.setInt(2, event.getEventNumber());
            statement.executeUpdate();
            log.info("Event {} deleted.", event);
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public NavigableSet<User> getEnrolled(Course course) throws SQLException {
        log.trace("getEnrolled({}) entered", course);
        if (course.getId() == null) {
            throw new IllegalArgumentException("Course id does not exists!");
        }
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.prepareStatement(
                    "SELECT * FROM " + DB_COURSE_TABLE
                    + " NATURAL JOIN " + DB_COURSE_STUDENT_TABLE
                    + " NATURAL JOIN " + UserManager.DB_USER_TABLE
                    + " WHERE " + DB_COURSE_ID_COLUMN + "=? ");
            statement.setInt(1, course.getId());

            ResultSet rs = statement.executeQuery();
            log.debug("statement {} executed", statement);
            NavigableSet<User> participants = new TreeSet<User>();
            while (rs.next()) {
                Integer uco = rs.getInt(UserManager.DB_USER_UCO_COLUMN);
                String name = rs.getString(UserManager.DB_USER_NAME_COLUMN);
                Boolean admin = rs.getBoolean(UserManager.DB_USER_ADMINISTRATOR_COLUMN);
                participants.add(new User(uco, name, admin));
            }
            return participants;
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public NavigableSet<User> getAttendees(Event event) throws SQLException {
        log.trace("getAttendees({}) entered", event);
        if (event.getEventNumber() == null) {
            throw new IllegalArgumentException("Number for event does not exists!");
        }
        if (event.getCourse().getId() == null) {
            throw new IllegalArgumentException("Course id for event does not exists!");
        }
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.prepareStatement(
                    "SELECT * FROM " + DB_EVENT_TABLE
                    + " NATURAL JOIN " + DB_EVENT_USER_TABLE
                    + " NATURAL JOIN " + UserManager.DB_USER_TABLE
                    + " WHERE " + DB_COURSE_ID_COLUMN + "=? AND "
                    + DB_EVENT_NUMBER_COLUMN + "=? ");
            statement.setInt(1, event.getCourse().getId());
            statement.setInt(2, event.getEventNumber());

            ResultSet rs = statement.executeQuery();
            log.debug("statement {} executed", statement);
            NavigableSet<User> attendees = new TreeSet<User>();
            while (rs.next()) {
                Integer uco = rs.getInt(UserManager.DB_USER_UCO_COLUMN);
                String name = rs.getString(UserManager.DB_USER_NAME_COLUMN);
                Boolean admin = rs.getBoolean(UserManager.DB_USER_ADMINISTRATOR_COLUMN);
                attendees.add(new User(uco, name, admin));
            }
            return attendees;
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void addAttendee(Event event, User user) throws SQLException, UserNotEnrolledException {
        log.trace("addAttendee({},{}) entered", event, user);
        if (event.getEventNumber() == null) {
            throw new IllegalArgumentException("Number for event does not exists!");
        }
        if (event.getCourse().getId() == null) {
            throw new IllegalArgumentException("Course id for event does not exists!");
        }
        if (user.getUco() <= 0) {
            throw new IllegalArgumentException("Uco for user does not exists!");
        }
        // check if enrolled:
        if (!getEnrolled(event.getCourse()).contains(user)) {
            throw new UserNotEnrolledException(user);
        }
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.prepareStatement(
                    "INSERT IGNORE INTO " + DB_EVENT_USER_TABLE + " ( "
                    + DB_COURSE_ID_COLUMN + " , "
                    + DB_EVENT_NUMBER_COLUMN + " , "
                    + UserManager.DB_USER_UCO_COLUMN + " ) VALUES (?,?,?)");
            statement.setInt(1, event.getCourse().getId());
            statement.setInt(2, event.getEventNumber());
            statement.setInt(3, user.getUco());
            statement.executeUpdate();
            log.debug("statement {} executed", statement);
            log.info("User {} attended {}.", user, event);
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void cancelAttendance(Event event, User user) throws SQLException {
        log.trace("cancelAttendance({},{}) entered", event, user);
        if (event.getEventNumber() == null) {
            throw new IllegalArgumentException("Number for event does not exists!");
        }
        if (event.getCourse().getId() == null) {
            throw new IllegalArgumentException("Course id for event does not exists!");
        }
        if (user.getUco() <= 0) {
            throw new IllegalArgumentException("Uco for user does not exists!");
        }
        PreparedStatement statement = null;
        Connection conn = null;
        try {
            //start Transaction
            conn = dataSource.getConnection();
            //delete Course (FK constraints will cascade delete)
            statement = conn.prepareStatement(
                    "DELETE FROM " + DB_EVENT_USER_TABLE
                    + " WHERE " + DB_COURSE_ID_COLUMN + "=? AND "
                    + DB_EVENT_NUMBER_COLUMN + "=? AND "
                    + UserManager.DB_USER_UCO_COLUMN + "=? ");
            statement.setInt(1, event.getCourse().getId());
            statement.setInt(2, event.getEventNumber());
            statement.setInt(3, user.getUco());
            statement.executeUpdate();
            log.info("User {} cancelled attendance for {}.", user, event);
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void enroll(Course course, User user) throws SQLException {
        log.trace("enroll({},{}) entered", course, user);
        if (course.getId() == null) {
            throw new IllegalArgumentException("Course id does not exists!");
        }
        if (user.getUco() <= 0) {
            throw new IllegalArgumentException("Uco for user does not exists!");
        }
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.prepareStatement(
                    "INSERT IGNORE INTO " + DB_COURSE_STUDENT_TABLE + " ( "
                    + DB_COURSE_ID_COLUMN + " , "
                    + UserManager.DB_USER_UCO_COLUMN + " ) VALUES (?,?)");
            statement.setInt(1, course.getId());
            statement.setInt(2, user.getUco());
            statement.executeUpdate();
            log.debug("statement {} executed", statement);
            log.info("User {} enrolled {}.", user, course);
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }

    public void cancelEnrollment(Course course, User user) throws SQLException {
        log.trace("cancelEnrollment({},{}) entered", course, user);
        if (course.getId() == null) {
            throw new IllegalArgumentException("Course id does not exists!");
        }
        if (user.getUco() <= 0) {
            throw new IllegalArgumentException("Uco for user does not exists!");
        }
        PreparedStatement statement = null;
        Connection conn = null;
        try {
            //start Transaction
            conn = dataSource.getConnection();
            //delete Course (FK constraints will cascade delete)
            statement = conn.prepareStatement(
                    "DELETE FROM " + DB_COURSE_STUDENT_TABLE
                    + " WHERE " + DB_COURSE_ID_COLUMN + "=? AND "
                    + UserManager.DB_USER_UCO_COLUMN + "=? ");
            statement.setInt(1, course.getId());
            statement.setInt(2, user.getUco());
            statement.executeUpdate();
            log.info("User {} cancelled enrollment for {}.", user, course);
        } finally {
            DBConnection.closeQuietly(statement);
            DBConnection.closeConnection(conn);
        }
    }
}
